/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.far.lib;

import com.lowagie.text.Element;
import com.lowagie.text.Font;
import com.lowagie.text.pdf.PdfPTable;
import java.sql.ResultSet;
import java.util.Iterator;
import java.util.List;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Jorge
 */
public class Excel {
    String xls = "";
    String subtitulo = "";
    int columnas = 1;
    int filas = 1;
    
    public Excel(){ }
    
    public Excel(String subt)
    {
        this.subtitulo = subt;
    }
            
    public void setEncabezado()
    {
        this.xls+="<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>\n";
        this.xls+="<?mso-application progid=\"Excel.Sheet\"?>\n";
        this.xls+="<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
        this.xls+=" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n";
        this.xls+=" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n";
        this.xls+=" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
        this.xls+=" xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n";
        this.xls+=" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\n";
        this.xls+="  <Author>Jorge Mueses</Author>\n";
        this.xls+="  <LastAuthor>Jorge Mueses</LastAuthor>\n";
        this.xls+="  <Created>2010-03-05T18:37:44Z</Created>\n";
        this.xls+="  <LastSaved>2010-03-05T18:40:43Z</LastSaved>\n";
        this.xls+="  <Company>YAKUSOFT</Company>\n";
        this.xls+="  <Version>15.00</Version>\n";
        this.xls+=" </DocumentProperties>\n";
        this.xls+=" <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">\n";
        this.xls+=" <AllowPNG/>\n";
        this.xls+=" </OfficeDocumentSettings>\n";
        this.xls+=" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n";
        this.xls+="  <WindowHeight>10200</WindowHeight>\n";
        this.xls+="  <WindowWidth>16455</WindowWidth>\n";
        this.xls+="  <WindowTopX>240</WindowTopX>\n";
        this.xls+="  <WindowTopY>30</WindowTopY>\n";
        this.xls+="  <ProtectStructure>False</ProtectStructure>\n";
        this.xls+="  <ProtectWindows>False</ProtectWindows>\n";
        this.xls+=" </ExcelWorkbook>\n";
        this.xls+=" <Styles>\n";
        this.xls+="  <Style ss:ID=\"Default\" ss:Name=\"Normal\">\n";
        this.xls+="   <Alignment ss:Vertical=\"Bottom\"/>\n";
        this.xls+="   <Borders/>\n";
        this.xls+="   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>\n";
        this.xls+="   <Interior/>\n";
        this.xls+="   <NumberFormat/>\n";
        this.xls+="   <Protection/>\n";
        this.xls+="  </Style>\n";
        this.xls+="  <Style ss:ID=\"s62\">\n";
        this.xls+="   <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\n";
        this.xls+="   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"\n";
        this.xls+="    ss:Bold=\"1\"/>\n";
        this.xls+="  </Style>\n";
        this.xls+=" </Styles>\n";
        this.xls+=" <Worksheet ss:Name=\"Hoja1\">\n";
        this.xls+="  <Table ss:ExpandedColumnCount=\"<<COLUMNAS>>\" ss:ExpandedRowCount=\"<<FILAS>>\" x:FullColumns=\"1\"\n";
        this.xls+="   x:FullRows=\"1\" ss:DefaultColumnWidth=\"50\" ss:DefaultRowHeight=\"15\">\n";
        this.xls+="   <Column ss:AutoFitWidth=\"0\" ss:Width=\"149.5\"/>\n";
    }
    
    public void setCabeceraTabla(String [] cabTabla)
    {
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        for(int i=0; i<this.columnas; i++){
            this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">"+cabTabla[i]+"</Data></Cell>\n";
        }
        this.xls+="   </Row>\n";
    }
    
    public void setCabeceraTabla(List tr)
    {
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        Iterator it = tr.iterator();
        while(it.hasNext()){
            this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">"+(String)it.next()+"</Data></Cell>\n";
        }
        this.xls+="   </Row>\n";
    }
    
    public String lista(ResultSet registros, String [] cabTabla, String [] tipo)
    {
        this.setEncabezado();
        this.columnas = cabTabla.length;
        this.xls = this.xls.replace("<<COLUMNAS>>", String.valueOf(this.columnas));
        
        this.xls+="   <Column ss:AutoFitWidth=\"0\" ss:Width=\"140.25\" ss:Span=\""+(this.columnas-2)+"\"/>\n";
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-1)+"\" ss:StyleID=\"s62\"><Data ss:Type=\"String\">"+this.subtitulo+"</Data></Cell>\n";
        this.xls+="   </Row>\n";

        this.setCabeceraTabla(cabTabla);
        try{
            while(registros.next()){
                this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
                for(int j=1; j<=this.columnas; j++){
                    String campo = (registros.getString(j)!=null)?registros.getString(j):"";
                    String tipo_dato = tipo[j-1];
                    this.xls+="    <Cell><Data ss:Type=\""+(campo.compareTo("")!=0 ? tipo_dato : "String")+"\">" + campo + "</Data></Cell>\n";
                }
                this.xls+="   </Row>\n";
                this.filas++;
            }
            registros.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        
        this.xls = this.xls.replace("<<FILAS>>", String.valueOf(this.filas+1));
            
        this.setPie();

        return this.xls;
    }

    public String lista(String maestro, String maestro1, ResultSet registros, String [] cabTabla, String [] tipo)
    {
        this.setEncabezado();
        this.columnas = cabTabla.length;
        this.xls = this.xls.replace("<<COLUMNAS>>", String.valueOf(this.columnas));
        
        this.xls+="   <Column ss:AutoFitWidth=\"0\" ss:Width=\"140.25\" ss:Span=\""+(this.columnas-2)+"\"/>\n";
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-1)+"\" ss:StyleID=\"s62\"><Data ss:Type=\"String\">"+this.subtitulo+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-1)+"\"><Data ss:Type=\"String\">"+maestro+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-1)+"\"><Data ss:Type=\"String\">"+maestro1+"</Data></Cell>\n";
        this.xls+="   </Row>\n";

        this.setCabeceraTabla(cabTabla);
        try{
            while(registros.next()){
                this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
                for(int j=1; j<=this.columnas; j++){
                    this.xls+="    <Cell><Data ss:Type=\""+tipo[j-1]+"\">" + ((registros.getString(j)!=null)?registros.getString(j):"") + "</Data></Cell>\n";
                }
                this.xls+="   </Row>\n";
                this.filas++;
            }
            registros.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        
        this.xls = this.xls.replace("<<FILAS>>", String.valueOf(this.filas+3));
            
        this.setPie();

        return this.xls;
    }
    
    public String auspicio(ResultSet rs, ResultSet rsActividades)
    {
        String num_auspicio = "";
        String fecha_creacion = "";
	String tipo_plan = "";
	String proveedor = "";
        String tipo_documento = "";
	String cedula = "";
	String contacto = "";
	String correo = "";
	String subtotal = "";
	String iva = "";
	String total = "";
	String dias_para_vencimiento = "";
	String observacion = "";
        try{
            if(rs.next()){
                num_auspicio = rs.getString("num_auspicio")!=null ? rs.getString("num_auspicio") : "";
                fecha_creacion = rs.getString("txt_fecha_creacion")!=null ? rs.getString("txt_fecha_creacion") : "";
                tipo_plan = rs.getString("tipo_plan")!=null ? rs.getString("tipo_plan") : "";
                proveedor = rs.getString("proveedor")!=null ? rs.getString("proveedor") : "";
                tipo_documento = rs.getString("tipo_documento")!=null ? rs.getString("tipo_documento") : "02";
                cedula = rs.getString("cedula")!=null ? rs.getString("cedula") : "";
                contacto = rs.getString("contacto")!=null ? rs.getString("contacto") : "";
                correo = rs.getString("correo")!=null ? rs.getString("correo") : "";
                subtotal = rs.getString("subtotal")!=null ? rs.getString("subtotal") : "";
                iva = rs.getString("iva")!=null ? rs.getString("iva") : "";
                total = rs.getString("total")!=null ? rs.getString("total") : "";
                dias_para_vencimiento = rs.getString("dias_para_vencimiento")!=null ? rs.getString("dias_para_vencimiento") : "";
                observacion = rs.getString("observacion")!=null ? rs.getString("observacion") : "";
                rs.close();
            }
        }catch(Exception e){
            e.printStackTrace();
        }
            
        this.setEncabezado();
        this.columnas = 8;
        this.xls = this.xls.replace("<<COLUMNAS>>", String.valueOf(this.columnas));
        
        this.xls+="   <Column ss:AutoFitWidth=\"0\" ss:Width=\"140.25\" ss:Span=\""+(this.columnas-2)+"\"/>\n";
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-1)+"\" ss:StyleID=\"s62\"><Data ss:Type=\"String\">Convenio de Auspicio</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">Nro. de auspicio:</Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+num_auspicio+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">Tipo de plan de mercadeo:</Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+tipo_plan+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">Fecha de creación:</Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+fecha_creacion+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">Proveedor: </Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+proveedor+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">Contacto: </Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+contacto+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
            
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">"+(tipo_documento.compareTo("02")==0 ? "Cédula:" : "Pasaporte:")+"</Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+cedula+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
            
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">Correo: </Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+correo+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">Plan de Actividades</Data></Cell>\n";
        this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">Desde</Data></Cell>\n";
        this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">Hasta</Data></Cell>\n";
        this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">Graba IVA</Data></Cell>\n";
        this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">Sub-Total</Data></Cell>\n";
        this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">IVA</Data></Cell>\n";
        this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">Total</Data></Cell>\n";
        this.xls+="    <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">Forma de Pago</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;

        try{
            String plan_actividad="";
            String txt_fecha_desde="";
            String txt_fecha_hasta="";
            String graba_iva="";
            String asubtotal="";
            String aiva="";
            String atotal="";
            String forma_pago="";
            while(rsActividades.next()){
                plan_actividad = rsActividades.getString("plan_actividad")!=null ? rsActividades.getString("plan_actividad") : "";
                txt_fecha_desde = rsActividades.getString("txt_fecha_desde")!=null ? rsActividades.getString("txt_fecha_desde") : "";
                txt_fecha_hasta = rsActividades.getString("txt_fecha_hasta")!=null ? rsActividades.getString("txt_fecha_hasta") : "";
                graba_iva = rsActividades.getString("graba_iva")!=null ? rsActividades.getString("graba_iva") : "";
                asubtotal = rsActividades.getString("subtotal")!=null ? rsActividades.getString("subtotal") : "";
                aiva = rsActividades.getString("iva")!=null ? rsActividades.getString("iva") : "";
                atotal = rsActividades.getString("total")!=null ? rsActividades.getString("total") : "";
                forma_pago = rsActividades.getString("forma_pago")!=null ? rsActividades.getString("forma_pago") : "";
                this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
                this.xls+="    <Cell><Data ss:Type=\"String\">"+plan_actividad+"</Data></Cell>\n";
                this.xls+="    <Cell><Data ss:Type=\"String\">"+txt_fecha_desde+"</Data></Cell>\n";
                this.xls+="    <Cell><Data ss:Type=\"String\">"+txt_fecha_hasta+"</Data></Cell>\n";
                this.xls+="    <Cell><Data ss:Type=\"String\">"+graba_iva+"</Data></Cell>\n";
                this.xls+="    <Cell><Data ss:Type=\"Number\">"+asubtotal+"</Data></Cell>\n";
                this.xls+="    <Cell><Data ss:Type=\"Number\">"+aiva+"</Data></Cell>\n";
                this.xls+="    <Cell><Data ss:Type=\"Number\">"+atotal+"</Data></Cell>\n";
                this.xls+="    <Cell><Data ss:Type=\"String\">"+forma_pago+"</Data></Cell>\n";
                this.xls+="   </Row>\n";
                this.filas++;
            }
            this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
            this.xls+="    <Cell><Data ss:Type=\"String\"> </Data></Cell>\n";
            this.xls+="    <Cell><Data ss:Type=\"String\"> </Data></Cell>\n";
            this.xls+="    <Cell><Data ss:Type=\"String\"> </Data></Cell>\n";
            this.xls+="    <Cell><Data ss:Type=\"String\">Total</Data></Cell>\n";
            this.xls+="    <Cell><Data ss:Type=\"Number\">"+subtotal+"</Data></Cell>\n";
            this.xls+="    <Cell><Data ss:Type=\"Number\">"+iva+"</Data></Cell>\n";
            this.xls+="    <Cell><Data ss:Type=\"Number\">"+total+"</Data></Cell>\n";
            this.xls+="    <Cell><Data ss:Type=\"String\"> </Data></Cell>\n";
            this.xls+="   </Row>\n";
            this.filas++;
            
        }catch(Exception e){
            e.printStackTrace();
        }
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">Fecha de pago</Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+dias_para_vencimiento+" días</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
        
        this.xls+="   <Row ss:AutoFitHeight=\"0\">\n";
        this.xls+="    <Cell><Data ss:Type=\"String\">Observaciones: </Data></Cell>\n";
        this.xls+="    <Cell ss:MergeAcross=\""+(this.columnas-2)+"\"><Data ss:Type=\"String\">"+observacion+"</Data></Cell>\n";
        this.xls+="   </Row>\n";
        this.filas++;
            
        this.xls = this.xls.replace("<<FILAS>>", String.valueOf(this.filas));
            
        this.setPie();

        return this.xls;
    }

    public String truncar(float num)
    {
        if(num>0){
            num = num + 0.0009f;
        }
        String cad2 = String.valueOf(num).replace(".", ":");
        String cad[] = cad2.split(":");
        String res = "";
        if(cad.length>1){
            cad[1] += "000";
            res = cad[1].substring(0,2);
        }
        return cad[0]+"."+res;
    }
    
    public void setPie()
    {
        this.xls+="  </Table>\n";
        this.xls+="  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\n";
        this.xls+="   <PageSetup>\n";
        this.xls+="    <Header x:Margin=\"0.3\"/>\n";
        this.xls+="    <Footer x:Margin=\"0.3\"/>\n";
        this.xls+="    <PageMargins x:Bottom=\"0.75\" x:Left=\"0.7\" x:Right=\"0.7\" x:Top=\"0.75\"/>\n";
        this.xls+="   </PageSetup>\n";
        this.xls+="   <Unsynced/>\n";
        this.xls+="   <Print>\n";
        this.xls+="    <ValidPrinterInfo/>\n";
        this.xls+="    <PaperSizeIndex>9</PaperSizeIndex>\n";
        this.xls+="    <HorizontalResolution>600</HorizontalResolution>\n";
        this.xls+="    <VerticalResolution>600</VerticalResolution>\n";
        this.xls+="   </Print>\n";
        this.xls+="   <Selected/>\n";
        this.xls+="   <Panes>\n";
        this.xls+="    <Pane>\n";
        this.xls+="     <Number>1</Number>\n";
        this.xls+="    </Pane>\n";
        this.xls+="   </Panes>\n";
        this.xls+="   <ProtectObjects>False</ProtectObjects>\n";
        this.xls+="   <ProtectScenarios>False</ProtectScenarios>\n";
        this.xls+="  </WorksheetOptions>\n";
        this.xls+=" </Worksheet>\n";
        this.xls+="</Workbook>\n";
    }
}
